package db.client;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import o.客户类型;
import db.Database;
import dbservice.client.ClientDBService;
import po.AccountBookPo;
import po.ClientPo;
import po.Po;

public class ClientDB implements ClientDBService{
	
	private AccountBookPo ab;
	
	public ClientDB(AccountBookPo ab) throws SQLException {
		String creatTable="CREATE TABLE IF NOT EXISTS `seac2`.`"+ab.getName()+"_client` (`number` VARCHAR(45) NOT NULL,  `type` VARCHAR(45) NOT NULL, `class` INT NOT NULL,  `name` VARCHAR(45) NULL,  `phone` VARCHAR(45) NULL,  `address` VARCHAR(45) NULL,  `postcode` VARCHAR(45) NULL,  `email` VARCHAR(45) NULL,  `yingshouedu` BIGINT(0) NOT NULL,  `yingshou` BIGINT(0) NOT NULL,  `yingfu` BIGINT(0) NOT NULL,  `morenyewuyuan` VARCHAR(45) NULL,  PRIMARY KEY (`number`),UNIQUE INDEX `1col_UNIQUE` (`name` ASC));";
		Connection c = Database.getConnection();
		Statement s = c.createStatement();
		s.execute(creatTable);
		s.close();
		this.ab=ab;
	}

	/**
	 * 向客户列表添加客户
	 * @param client
	 * 需要添加的客户的详细信息
	 */
	public Po add(ClientPo client){
		Po po=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.executeUpdate("insert into "+ab.getName()+"_client values('"+client.get编号()+"','"+client.get类型().name()+"','"+client.get级别()+"','"+client.get姓名()+"','"+client.get电话()+"','"+client.get地址()+"','"+client.get邮编()+"','"+client.get电子邮箱()+"','"+client.get应收额度()+"','"+client.get应收()+"','"+client.get应付()+"','"+client.get默认业务员()+"');");
			s.close();
			po.successed();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}
		return po;
	}
	
	/**
	 * 删除与参数同名称的客户
	 * @para keyword
	 */
	public Po delete(ClientPo client){
		Po po=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			if(s.executeUpdate("delete from "+ab.getName()+"_client where number='"+client.get编号()+"';")==0){
				po.failed("客户不存在");
			}else{
				po.successed();
			}
			s.close();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}		
		return po;
	}
	
	/**
	 * 更新与参数同名称的客户的信息
	 * @param client
	 * 新的客户信息
	 * @return 是否更新成功
	 */
	public Po update(ClientPo client){
		Po po=new Po();		
		Connection c=Database.getConnection();
		Statement s;
		try {
			if(client.get编号()==null){
				throw new NullPointerException();
			}
			s = c.createStatement();
			if(client.get地址()!=null){
				s.addBatch(("update "+ab.getName()+"_client set address='"+client.get地址()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get姓名()!=null){
				s.addBatch(("update "+ab.getName()+"_client set name='"+client.get姓名()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get电子邮箱()!=null){
				s.addBatch(("update "+ab.getName()+"_client set email='"+client.get电子邮箱()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get电话()!=null){
				s.addBatch(("update "+ab.getName()+"_client set phone='"+client.get电话()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get类型()!=null){
				s.addBatch(("update "+ab.getName()+"_client set type='"+client.get类型().name()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get邮编()!=null){
				s.addBatch(("update "+ab.getName()+"_client set postcode='"+client.get邮编()+"' where number='"+client.get编号()+"';"));
			}
			if(client.get默认业务员()!=null){
				s.addBatch(("update "+ab.getName()+"_client set morenyewuyuan='"+client.get默认业务员()+"' where number='"+client.get编号()+"';"));
			}			
			s.addBatch(("update "+ab.getName()+"_client set class='"+client.get级别()+"' where number='"+client.get编号()+"';"));
			s.addBatch(("update "+ab.getName()+"_client set yingshouedu='"+client.get应收额度()+"' where number='"+client.get编号()+"';"));
			s.addBatch(("update "+ab.getName()+"_client set yingshou='"+client.get应收()+"' where number='"+client.get编号()+"';"));
			s.addBatch(("update "+ab.getName()+"_client set yingfu='"+client.get应付()+"' where number='"+client.get编号()+"';"));
			s.executeBatch();
			s.close();
			po.successed();
		} catch (SQLException e) {
			po.failed(e.getMessage());
			e.printStackTrace();
		}		
		return po;
	}

	@Override
	public List<ClientPo> getAll() {
		List<ClientPo> list=new ArrayList<ClientPo>();
		Connection c=Database.getConnection();
		try {			
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from "+ab.getName()+"_client;");
			while(r.next()){
				ClientPo po=new ClientPo();
				po.set编号(r.getString(1));
				po.set类型(客户类型.valueOf(r.getString(2)));
				po.set级别(r.getInt(3));
				po.set姓名(r.getString(4));
				po.set电话(r.getString(5));
				po.set地址(r.getString(6));
				po.set邮编(r.getString(7));
				po.set电子邮箱(r.getString(8));
				po.set应收额度(r.getLong(9));
				po.set应收(r.getLong(10));
				po.set应付(r.getLong(11));
				po.set默认业务员(r.getString(12));
				list.add(po);
			}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return list;
	}

	@Override
	public String getBiggestId() {
		String result=null;
		Connection c=Database.getConnection();
		try {			
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select max(number) from "+ab.getName()+"_client;");
			if(r.next()){
				result=r.getString(1);
			}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return result;
	}

	@Override
	public List<ClientPo> find(ClientPo clientPo) {
		List<ClientPo> list=new ArrayList<ClientPo>();
		Connection c=Database.getConnection();
		try {			
			Statement s = c.createStatement();
			ResultSet r=s.executeQuery("select * from "+ab.getName()+"_client where name like '%"+clientPo.get姓名()+"%';");
			while(r.next()){
				ClientPo po=new ClientPo();
				po.set编号(r.getString(1));
				po.set类型(客户类型.valueOf(r.getString(2)));
				po.set级别(r.getInt(3));
				po.set姓名(r.getString(4));
				po.set电话(r.getString(5));
				po.set地址(r.getString(6));
				po.set邮编(r.getString(7));
				po.set电子邮箱(r.getString(8));
				po.set应收额度(r.getLong(9));
				po.set应收(r.getLong(10));
				po.set应付(r.getLong(11));
				po.set默认业务员(r.getString(12));
				list.add(po);
			}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}		
		return list;
	}
	
}
